import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { ResultUtils } from 'src/common/utils/result.utils';

@Injectable()
export class HomeService {
    constructor(private readonly dataSource: DataSource) {};
    async find() {
        const articleNumResult = await this.dataSource.query(`SELECT COUNT(*) AS articleNum FROM article`);
        const articleViewsResult = await this.dataSource.query(`SELECT SUM(view) AS articleViews FROM article`);
        const articleTopResult = await this.dataSource.query(`SELECT id,title,view FROM article ORDER BY view DESC LIMIT 10`);
        const classifyTopResult = await this.dataSource.query(`SELECT c.id AS classifyId,name AS classifyName,IFNULL(SUM(DISTINCT a.view),0) AS classifyView FROM classify as c 
                                                              LEFT JOIN article as a ON c.id=a.classify_id GROUP BY c.id ORDER BY IFNULL(SUM(DISTINCT a.view),0) DESC LIMIT 10`);
        const TagTopResult = await this.dataSource.query(`SELECT t.id as tagId,t.name as tagName,IFNULL(SUM(DISTINCT a.view),0) AS tagView FROM tag as t 
                                                          LEFT JOIN article_tag as at ON t.id = at.t_id 
                                                          LEFT JOIN article as a ON a.id = at.a_id
                                                          GROUP BY t.id ORDER BY IFNULL(SUM(DISTINCT a.view),0) DESC LIMIT 10`);
        const result = {
            articleNum:parseInt(articleNumResult[0].articleNum),
            articleViews:parseInt(articleViewsResult[0].articleViews),
            topArticle:articleTopResult,
            topClassify:classifyTopResult,
            topTag:TagTopResult
        };
        return ResultUtils.success(result);
    }
}
